import sys
import os

# 添加项目根目录到系统路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from database import Database

class AllocationQuery:
    def __init__(self):
        self.db = Database()
    
    def query_student_allocation(self, student_id):
        """
        查询学生的宿舍分配记录
        
        参数:
            student_id (str): 学生学号
        
        返回:
            list: 分配记录列表
        """
        try:
            self.db.connect()
            
            # 检查学生是否存在
            self.db.cursor.execute(
                "SELECT id, name FROM student WHERE student_id = ?",
                (student_id,)
            )
            student = self.db.cursor.fetchone()
            if not student:
                print(f"错误：学号为 {student_id} 的学生不存在")
                return []
            
            student_db_id, student_name = student
            
            # 查询学生的所有分配记录
            self.db.cursor.execute(
                """SELECT a.id, a.allocation_date, a.status, d.building, d.room_number 
                FROM allocation a 
                JOIN dormitory d ON a.dormitory_id = d.id 
                WHERE a.student_id = ? 
                ORDER BY a.allocation_date DESC""",
                (student_db_id,)
            )
            allocations = self.db.cursor.fetchall()
            
            if not allocations:
                print(f"学生 {student_name} 没有任何宿舍分配记录")
            
            return allocations, student_name
        except Exception as e:
            print(f"错误：查询宿舍分配记录时发生错误 - {str(e)}")
            return [], None
        finally:
            self.db.close()
    
    def query_dormitory_allocation(self, dormitory_id):
        """
        查询宿舍的分配记录
        
        参数:
            dormitory_id (int): 宿舍ID
        
        返回:
            list: 分配记录列表
        """
        try:
            self.db.connect()
            
            # 检查宿舍是否存在
            self.db.cursor.execute(
                "SELECT building, room_number FROM dormitory WHERE id = ?",
                (dormitory_id,)
            )
            dormitory = self.db.cursor.fetchone()
            if not dormitory:
                print(f"错误：ID为 {dormitory_id} 的宿舍不存在")
                return []
            
            building, room_number = dormitory
            
            # 查询宿舍的所有分配记录
            self.db.cursor.execute(
                """SELECT a.id, a.allocation_date, a.status, s.student_id, s.name 
                FROM allocation a 
                JOIN student s ON a.student_id = s.id 
                WHERE a.dormitory_id = ? 
                ORDER BY a.allocation_date DESC""",
                (dormitory_id,)
            )
            allocations = self.db.cursor.fetchall()
            
            if not allocations:
                print(f"宿舍 {building}-{room_number} 没有任何分配记录")
            
            return allocations, f"{building}-{room_number}"
        except Exception as e:
            print(f"错误：查询宿舍分配记录时发生错误 - {str(e)}")
            return [], None
        finally:
            self.db.close()
    
    def query_current_dormitory_residents(self, dormitory_id):
        """
        查询宿舍当前入住的学生
        
        参数:
            dormitory_id (int): 宿舍ID
        
        返回:
            list: 学生列表
        """
        try:
            self.db.connect()
            
            # 检查宿舍是否存在
            self.db.cursor.execute(
                "SELECT building, room_number, capacity, current_count FROM dormitory WHERE id = ?",
                (dormitory_id,)
            )
            dormitory = self.db.cursor.fetchone()
            if not dormitory:
                print(f"错误：ID为 {dormitory_id} 的宿舍不存在")
                return []
            
            building, room_number, capacity, current_count = dormitory
            
            # 查询当前入住的学生
            self.db.cursor.execute(
                """SELECT s.id, s.student_id, s.name, s.gender, s.class_name, s.contact 
                FROM student s 
                WHERE s.dormitory_id = ?""",
                (dormitory_id,)
            )
            students = self.db.cursor.fetchall()
            
            return students, f"{building}-{room_number}", capacity, current_count
        except Exception as e:
            print(f"错误：查询宿舍入住情况时发生错误 - {str(e)}")
            return [], None, 0, 0
        finally:
            self.db.close()
    
    def query_all_allocations(self, limit=50):
        """
        查询所有分配记录
        
        参数:
            limit (int): 限制返回的记录数量
        
        返回:
            list: 分配记录列表
        """
        try:
            self.db.connect()
            
            # 查询最近的分配记录
            self.db.cursor.execute(
                """SELECT a.id, a.allocation_date, a.status, 
                s.student_id, s.name, 
                d.building, d.room_number 
                FROM allocation a 
                JOIN student s ON a.student_id = s.id 
                JOIN dormitory d ON a.dormitory_id = d.id 
                ORDER BY a.allocation_date DESC 
                LIMIT ?""",
                (limit,)
            )
            allocations = self.db.cursor.fetchall()
            
            return allocations
        except Exception as e:
            print(f"错误：查询宿舍分配记录时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_allocation_by_student(self, student_id):
        """
        根据学生学号查询宿舍分配记录
        
        参数:
            student_id (str): 学生学号
        
        返回:
            list: 分配记录列表，每条记录包含[分配ID, 学生学号, 学生姓名, 宿舍楼号, 宿舍房间号, 分配状态, 分配日期]
        """
        try:
            self.db.connect()
            
            # 检查学生是否存在
            self.db.cursor.execute(
                "SELECT id, name FROM student WHERE student_id = ?",
                (student_id,)
            )
            student = self.db.cursor.fetchone()
            if not student:
                print(f"错误：学号为 {student_id} 的学生不存在")
                return []
            
            student_db_id, student_name = student
            
            # 查询学生的所有宿舍分配记录（包括当前和历史记录）
            self.db.cursor.execute(
                """SELECT a.id, s.student_id, s.name, d.building, d.room_number, a.status, a.allocation_date 
                FROM allocation a 
                JOIN student s ON a.student_id = s.id 
                JOIN dormitory d ON a.dormitory_id = d.id 
                WHERE s.student_id = ?
                ORDER BY a.allocation_date DESC""",
                (student_id,)
            )
            allocations = self.db.cursor.fetchall()
            
            return allocations
        except Exception as e:
            print(f"错误：查询学生宿舍分配记录时发生错误 - {str(e)}")
            return None
        finally:
            self.db.close()
    
    def query_allocation_by_dormitory(self, dormitory_id):
        """
        根据宿舍ID查询分配记录
        
        参数:
            dormitory_id (int): 宿舍ID
        
        返回:
            list: 分配记录列表，每条记录包含[分配ID, 学生学号, 学生姓名, 宿舍楼号, 宿舍房间号]
        """
        try:
            self.db.connect()
            
            # 检查宿舍是否存在
            self.db.cursor.execute(
                "SELECT building, room_number FROM dormitory WHERE id = ?",
                (dormitory_id,)
            )
            dormitory = self.db.cursor.fetchone()
            if not dormitory:
                print(f"错误：ID为 {dormitory_id} 的宿舍不存在")
                return []
            
            building, room_number = dormitory
            
            # 查询宿舍的当前分配记录
            self.db.cursor.execute(
                """SELECT a.id, s.student_id, s.name, d.building, d.room_number 
                FROM allocation a 
                JOIN student s ON a.student_id = s.id 
                JOIN dormitory d ON a.dormitory_id = d.id 
                WHERE a.dormitory_id = ? AND a.status = 'active'
                ORDER BY a.allocation_date DESC""",
                (dormitory_id,)
            )
            allocations = self.db.cursor.fetchall()
            
            return allocations
        except Exception as e:
            print(f"错误：查询宿舍分配记录时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()

# 如果直接运行此文件，提供命令行界面
if __name__ == "__main__":
    query = AllocationQuery()
    
    print("===== 宿舍分配查询 =====")
    print("1. 查询学生的宿舍分配记录")
    print("2. 查询宿舍的分配记录")
    print("3. 查询宿舍当前入住情况")
    print("4. 查询所有分配记录")
    
    choice = input("请选择查询方式 (1-4): ")
    
    if choice == '1':
        student_id = input("请输入学生学号: ")
        allocations, student_name = query.query_student_allocation(student_id)
        
        if allocations and student_name:
            print(f"\n学生 {student_name} 的宿舍分配记录:")
            print("记录ID\t分配日期\t\t状态\t\t宿舍")
            for allocation in allocations:
                status = "当前住宿" if allocation[2] == 'active' else "已退宿" if allocation[2] == 'checkout' else "历史记录"
                print(f"{allocation[0]}\t{allocation[1]}\t{status}\t{allocation[3]}-{allocation[4]}")
    
    elif choice == '2':
        try:
            dormitory_id = int(input("请输入宿舍ID: "))
            allocations, dormitory_name = query.query_dormitory_allocation(dormitory_id)
            
            if allocations and dormitory_name:
                print(f"\n宿舍 {dormitory_name} 的分配记录:")
                print("记录ID\t分配日期\t\t状态\t\t学号\t姓名")
                for allocation in allocations:
                    status = "当前住宿" if allocation[2] == 'active' else "已退宿" if allocation[2] == 'checkout' else "历史记录"
                    print(f"{allocation[0]}\t{allocation[1]}\t{status}\t{allocation[3]}\t{allocation[4]}")
        except ValueError:
            print("错误：宿舍ID必须是数字")
    
    elif choice == '3':
        try:
            dormitory_id = int(input("请输入宿舍ID: "))
            students, dormitory_name, capacity, current_count = query.query_current_dormitory_residents(dormitory_id)
            
            if dormitory_name:
                print(f"\n宿舍 {dormitory_name} 当前入住情况 ({current_count}/{capacity}):")
                if students:
                    print("学生ID\t学号\t姓名\t性别\t班级\t联系方式")
                    for student in students:
                        print(f"{student[0]}\t{student[1]}\t{student[2]}\t{student[3]}\t{student[4]}\t{student[5] or '无'}")
                else:
                    print("该宿舍当前没有学生入住")
        except ValueError:
            print("错误：宿舍ID必须是数字")
    
    elif choice == '4':
        allocations = query.query_all_allocations()
        
        if allocations:
            print("\n最近的宿舍分配记录:")
            print("记录ID\t分配日期\t\t状态\t\t学号\t姓名\t宿舍")
            for allocation in allocations:
                status = "当前住宿" if allocation[2] == 'active' else "已退宿" if allocation[2] == 'checkout' else "历史记录"
                print(f"{allocation[0]}\t{allocation[1]}\t{status}\t{allocation[3]}\t{allocation[4]}\t{allocation[5]}-{allocation[6]}")
        else:
            print("没有找到任何分配记录")
    
    else:
        print("无效的选择")